﻿using CPFrameWork.Global;
using CPFrameWork.UIInterface.Grid;
using CPFrameWork.Utility;
using CPFrameWork.Utility.DbOper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Reflection;
using System.Text;

namespace BusinessManage.OrderFormCable
{
    public class OrderFormCable
    {
        /// <summary>
        /// 设置任务分配车辆信息
        /// </summary>
        /// <returns></returns>
        public CPWebApiBaseReturnEntity SetYW_TaskCarRelation(int carid,int CurUserId, string GridCode,DataRow[] orderdr, string taskIdD,DataTable mainuserTable, DataTable assuserTable,int BuyClickCount,int HallLossCount, List<long> TaskOrder)
        {
            CPWebApiBaseReturnEntity re = new CPWebApiBaseReturnEntity();
            try
            {

                CPGrid grid = CPGridEngine.Instance(CurUserId).GetGrid(GridCode, false, false);
                DbHelper dbHelper = new DbHelper(grid.DbIns, CPAppContext.CurDbType());
                #region  获取车辆信息
                StringBuilder stringBuilder = new StringBuilder();
                string sql = @"select Id,CarNumber,MainDriverId,MainDriverName,AssistantDriverId,AssistantDriverName,MainDriverTel,AssistantDriverTel,
                               CarType,SensorId,GPSId,CarTeamNumber,CarTeamName,BaseId,BaseName,CreateUserId,CreateTime,ModifyUserId,ModifyTime
                               from dbo.BD_CarInfo where id=" + Convert.ToInt32(carid);
                stringBuilder.Append(sql);
                DataTable dt = dbHelper.ExecuteDataSet(stringBuilder.ToString()).Tables[0];
                #endregion

                for (int i = 0,j = 0; i< orderdr.Length; i++)
                {
 
                    DataRow datar = orderdr[i];
                    #region 判断当前分厅信息是否存在
                    StringBuilder build = new StringBuilder();
                    string strsql = @"select id,TaskId,OrderId,GivetohallId from dbo.YW_TaskOrderRelation where taskid not in ('" + taskIdD + "')  and  GivetohallId='" + datar["code"] + "'";
                    build.Append(strsql);
                    DataTable dat = dbHelper.ExecuteDataSet(build.ToString()).Tables[0];
                    if (dat.Rows.Count > 0)
                    {
                        continue;
                    }
                    #endregion               


                    int buyClick =Convert.ToInt32( datar["BuyChickCount"]);
                    //int TotalCount = Convert.ToInt32(datar["TotalCount"]);
                    if ((buyClick != 0 && BuyClickCount > buyClick) || (BuyClickCount == 0 && BuyClickCount > buyClick) || orderdr.Length > 1) BuyClickCount = buyClick;

                    int hallLoss = Convert.ToInt32(datar["HallLossCount"]);
                    int OrderRoadLoss = Convert.ToInt32(datar["OrderRoadLoss"]);
                    if ((hallLoss != 0 && HallLossCount > hallLoss) || (HallLossCount == 0 && HallLossCount > hallLoss) || orderdr.Length > 1) HallLossCount = hallLoss;
                    StringBuilder sb = new StringBuilder();
                    //string total = (datar["TotalCount"].ToString() == "") ? "0" : datar["TotalCount"].ToString();
                   // string loss = (datar["OrderRoadLoss"].ToString() == "") ? "0" : datar["OrderRoadLoss"].ToString();
                    string[] pkArray = grid.PKFieldName.Split(',');
                    string strSql = "";
                    strSql = @"INSERT INTO   dbo.YW_TaskCarRelation
                        (TaskId,OrderId,TaskOrderId,CarNumber,SensorId,GPSId,MainDriverId,MainDriverName,MainDriverTel,AssistantDriverId,AssistantDriverName,AssistantDriverTel,OrderChickCountInCar,BaseId,BaseName,HallId,HallName,HallLossCount)
                         VALUES " +
                    " ('" + taskIdD + "','" + datar["OrderCode"].ToString() + "'," + TaskOrder[j] + ",'" + dt.Rows[0]["CarNumber"].ToString() + "','" + dt.Rows[0]["SensorId"].ToString() + "','" + dt.Rows[0]["GPSId"].ToString() + "'," +
                    "'" + mainuserTable.Rows[0]["UserId"].ToString() + "','" + mainuserTable.Rows[0]["UserName"].ToString() + "'," +
                    "'" + mainuserTable.Rows[0]["MobilePhone"].ToString() + "','" + assuserTable.Rows[0]["UserId"].ToString() + "','" + assuserTable.Rows[0]["UserName"].ToString() + "','" + assuserTable.Rows[0]["MobilePhone"] + "'," + BuyClickCount +
                    ",'" + datar["BaseId"] + "','" + datar["BaseName"] + "','" + datar["HallId"] + "','" + datar["HallName"] + "'," + HallLossCount + ")";
                    sb.Append(strSql);


                    if (sb.Length > 0)
                    {
                        dbHelper.ExecuteNonQuery(sb.ToString());
                    }
                    j++;
                }
                re.Result = true;
                return re;
            }
            catch (Exception ex)
            {
                re.Result = false;
                re.ErrorMsg = ex.Message.ToString();
                return re;
            }
        }

        public void ClearTaskCar(int CurUserId, string GridCode, DataRow[] orderdr, string taskIdD, List<long> TaskOrder)
        {

            for (int i = 0; i < orderdr.Length; i++)
            {
                DataRow datar = orderdr[i];
                for (int j = 0; j < TaskOrder.Count; j++)
                {

                    #region 清空原来的车辆信息，重新设置
                    CPGrid grid = CPGridEngine.Instance(CurUserId).GetGrid(GridCode, false, false);
                    DbHelper dbHelper = new DbHelper(grid.DbIns, CPAppContext.CurDbType());
                    StringBuilder strBuid = new StringBuilder();
                    string carsql = @"select id,TaskId,OrderId,TaskOrderId from dbo.YW_TaskCarRelation where taskid='" + taskIdD + "' and  OrderId='" + datar["OrderCode"] + "' and TaskOrderId='" + TaskOrder[j] + "'";
                    strBuid.Append(carsql);
                    DataTable datat = dbHelper.ExecuteDataSet(strBuid.ToString()).Tables[0];
                    if (datat.Rows.Count > 0)
                    {
                        StringBuilder delstrBuid = new StringBuilder();
                        string delcarsql = @"DELETE  from dbo.YW_TaskCarRelation where taskid='" + taskIdD + "' and  OrderId='" + datar["OrderCode"] + "' and TaskOrderId='" + TaskOrder[j] + "'";
                        delstrBuid.Append(delcarsql);
                        dbHelper.ExecuteNonQuery(delstrBuid.ToString());
                    }
                    #endregion
                }
            }
           
     
        }

        /// <summary>
        /// 通过订单返回订单信息
        /// </summary>
        /// <param name="CurUserId"></param>
        /// <param name="orderid"></param>
        /// <param name="GridCode"></param>
        /// <returns></returns>
        public DataTable GetOrderInfo(int CurUserId, string orderid, string hallid, string GridCode)
        {
            CPGrid grid = CPGridEngine.Instance(CurUserId).GetGrid(GridCode, false, false);
            DbHelper dbHelper = new DbHelper(grid.DbIns, CPAppContext.CurDbType());

            #region 获取订单信息
            StringBuilder orderBuilder = new StringBuilder();
            string ordersql = string.Empty;
            if (string.IsNullOrEmpty(orderid))
                ordersql = @"select b.id code,a.Id,OrderCode,OrderStatus,OrgId,OrgName,SaleDeptId,SaleDeptName,SaleGroupId,SaleGroupName,MarkId,MarketName,
                                   AgentId,AgentName,AgentTel,Salesman,SalesmanTel,RegionalAccountantId,RegionalAccountantName,CustomerName,CustomerState,
                                   CustomerCity,CustomerDistrict,CustomerAddress,CustomerZip,CustomerMobile,CustomerPhone,TotalCount,OrderRoadLoss,WaitDeliveryTime,
                                   b.BaseId,b.BaseName,b.HallId,b.HallName,b.VarietyId,b.VarietyName,b.GoodsId,b.GoodsName,b.BuyChickCount,isnull(b.HallLossCount,0) HallLossCount
                                  from dbo.YW_OrderInfo a  inner join dbo.YW_GivetohallInfo b on a.OrderCode=b.OrderId where b.Id in (" + hallid + ")";
            else
                ordersql = @"select b.id code,a.Id,OrderCode,OrderStatus,OrgId,OrgName,SaleDeptId,SaleDeptName,SaleGroupId,SaleGroupName,MarkId,MarketName,
                                   AgentId,AgentName,AgentTel,Salesman,SalesmanTel,RegionalAccountantId,RegionalAccountantName,CustomerName,CustomerState,
                                   CustomerCity,CustomerDistrict,CustomerAddress,CustomerZip,CustomerMobile,CustomerPhone,TotalCount,OrderRoadLoss,WaitDeliveryTime,
                                   b.BaseId,b.BaseName,b.HallId,b.HallName,b.VarietyId,b.VarietyName,b.GoodsId,b.GoodsName,b.BuyChickCount,isnull(b.HallLossCount,0) HallLossCount
                                  from dbo.YW_OrderInfo a  inner join dbo.YW_GivetohallInfo b on a.OrderCode=b.OrderId where a.Id in (" + orderid + ")";

            orderBuilder.Append(ordersql);
            DataTable orderdt = dbHelper.ExecuteDataSet(orderBuilder.ToString()).Tables[0];
            #endregion
            return orderdt;
        }

        /// <summary>
        /// 通过单号获得分厅信息
        /// </summary>
        /// <param name="CurUserId"></param>
        /// <param name="orderid"></param>
        /// <param name="GridCode"></param>
        /// <returns></returns>
        public DataTable GetYW_GivetohallInfo(int CurUserId, string orderid, string GridCode)
        {
            CPGrid grid = CPGridEngine.Instance(CurUserId).GetGrid(GridCode, false, false);
            DbHelper dbHelper = new DbHelper(grid.DbIns, CPAppContext.CurDbType());
            StringBuilder orderBuilder = new StringBuilder();
            string ordersql = @"SELECT  [Id]
                                        ,[OrderId]
                                        ,[OrderChildId]
                                        ,[BaseId]
                                        ,[BaseName]
                                        ,[HallId]
                                        ,[HallName]
                                        ,[VarietyId]
                                        ,[VarietyName]
                                        ,[GoodsId]
                                        ,[GoodsName]
                                        ,[BuyChickCount]
                                        ,[HallLossCount]
                                        ,[CreateUserId]
                                        ,[CreateTime]
                                        ,[ModifyTime]
                                    FROM [dbo].[YW_GivetohallInfo] where OrderId  in (select OrderCode from dbo.YW_OrderInfo a  where a.Id in (" + orderid + ")) order by Id";
            orderBuilder.Append(ordersql);
            DataTable orderdt = dbHelper.ExecuteDataSet(orderBuilder.ToString()).Tables[0];
            return orderdt;
        }
        /// <summary>
        /// 配送任务预订单关系信息
        /// </summary>
        /// <param name="orderdt"></param>
        /// <returns></returns>
        public CPWebApiBaseReturnEntity SaveYW_TaskOrderRelation(DataRow[] orderdr,string taskid, int CurUserId,string GridCode,string Sorts, ref List<long> TaskOrder)
        {

            CPWebApiBaseReturnEntity re = new CPWebApiBaseReturnEntity();
            try
            {
                CPGrid grid = CPGridEngine.Instance(CurUserId).GetGrid(GridCode, false, false);
                DbHelper dbHelper = new DbHelper(grid.DbIns, CPAppContext.CurDbType());

                #region  最新任务表信息
                //StringBuilder stringBuilder = new StringBuilder();
                //string sql = @"select count(id) as  id from dbo.YW_TaskOrderRelation where TaskId in (select taskid from YW_TaskInfo where 
                //            (YEAR(CreateTime)=YEAR(GETDATE()) and MONTH(CreateTime)=MONTH(GETDATE()) and DAY(CreateTime)=DAY(GETDATE())) or  
                //            (YEAR(ModifyTime)=YEAR(GETDATE()) and MONTH(ModifyTime)=MONTH(GETDATE()) and DAY(ModifyTime)=DAY(GETDATE())))";
                //stringBuilder.Append(sql);
                //DataTable dt = dbHelper.ExecuteDataSet(stringBuilder.ToString()).Tables[0];
                //
                //int id = Convert.ToInt32(dt.Rows[0]["id"]) + 1;
                //string TaskId = string.Empty;
                //string yearstr = DateTime.Now.Year.ToString();
                //string monthstr = DateTime.Now.Month.ToString();
                //string daystr = DateTime.Now.Day.ToString();
                //long TaskOrderId = Convert.ToInt64(yearstr + monthstr + daystr + RightZeroReplaceChar(6, id.ToString()));
                #endregion


                string[] sortArray = Sorts.Split(new string[] { "$" }, StringSplitOptions.RemoveEmptyEntries);

                #region 配送任务基本信息
                for (int i=0;i< orderdr.Length;i++)                    
                {
                    int Sort = Convert.ToInt32(sortArray[i]);
                    DataRow orderrow = orderdr[i];
                    #region 判断当前分厅信息是否存在
                    StringBuilder build = new StringBuilder();
                    string sql = @"select id,TaskId,OrderId,GivetohallId from dbo.YW_TaskOrderRelation where taskid not in ('" + taskid + "')  and  GivetohallId='" + orderrow["code"] + "'";
                    build.Append(sql);
                    DataTable dat = dbHelper.ExecuteDataSet(build.ToString()).Tables[0];
                    if (dat.Rows.Count > 0)
                    {
                        continue;
                    }
                    #endregion
                    int autoIndex;
                    long TaskOrderId = Convert.ToInt64(CPAutoNumHelper.Instance().GetNextAutoNum("TaskOrderId", out autoIndex));

                    StringBuilder taskBuild = new StringBuilder();
                    string tasksql = @"select id,TaskId,OrderId,GivetohallId from dbo.YW_TaskOrderRelation where taskid='" + taskid + "' and  OrderId='" + orderrow["OrderCode"] + "' and GivetohallId='" + orderrow["code"] + "'";
                    taskBuild.Append(tasksql);
                    DataTable datat = dbHelper.ExecuteDataSet(taskBuild.ToString()).Tables[0];
                    if (datat.Rows.Count > 0)
                    {
                        TaskOrderId = Convert.ToInt64(datat.Rows[0]["id"]);
                        StringBuilder deltaskBuild = new StringBuilder();
                        string deltasksql = @"delete from dbo.YW_TaskOrderRelation where taskid='" + taskid + "' and  OrderId='" + orderrow["OrderCode"] + "' and GivetohallId='" + orderrow["code"] + "'";
                        deltaskBuild.Append(deltasksql);
                        dbHelper.ExecuteNonQuery(deltaskBuild.ToString());
                    }
                    

                   
                    StringBuilder taskBuilder = new StringBuilder();
                    string taskordersql = @"insert into dbo.YW_TaskOrderRelation
                                  (Id,TaskId,OrderId,Sort,OrderDeliveryStatus,IsMore,IsChange,BaseId,BaseName,HallId,HallName,GivetohallId,VarietyId,VarietyName,GoodsId,GoodsName)
                                  VALUES";
                    taskordersql += "("+ TaskOrderId + ",'" + taskid + "','" + orderrow["OrderCode"] + "'," + Sort + ",0,0,0,";
                    taskordersql += " '" + orderrow["BaseId"] + "','" + orderrow["BaseName"] + "','" + orderrow["HallId"] + "','" + orderrow["HallName"] + "',";
                    taskordersql += "'" + orderrow["code"] + "','" + orderrow["VarietyId"] + "','" + orderrow["VarietyName"] + "','" + orderrow["GoodsId"] + "','" + orderrow["GoodsName"] + "')";
                    taskBuilder.Append(taskordersql);
                    if (taskBuilder.Length > 0)
                    {
                        dbHelper.ExecuteNonQuery(taskBuilder.ToString());
                    }
                    TaskOrder.Add(TaskOrderId);
                }

               

                #endregion
                re.Result = true;
                return re;
            }
            catch (Exception ex)
            {
                re.Result = false;
                re.ErrorMsg = ex.Message.ToString();
                return re;
            }

        }


        /// <summary>
        /// 通过订单及任务编码更新车牌号
        /// </summary>
        /// <param name="taskid"></param>
        /// <param name="orderid"></param>
        /// <returns></returns>
        public void updateCarNumberForYW_TaskCarRelation(string taskid,List<long> TaskOrder, int CurUserId, string GridCode)
        {
            CPGrid grid = CPGridEngine.Instance(CurUserId).GetGrid(GridCode, false, false);
            DbHelper dbHelper = new DbHelper(grid.DbIns, CPAppContext.CurDbType());

            foreach (long TaskOrderId in TaskOrder)
            {
                #region 通过订单及任务号获取车牌信息
                StringBuilder taskcarBuilder = new StringBuilder();
                string tasksql = @"select CarNumber from dbo.YW_TaskCarRelation WHERE OrderId IN (SELECT OrderCode FROM dbo.YW_OrderInfo WHERE TaskOrderId=" + TaskOrderId + ") and TaskId='" + taskid + "'";
                taskcarBuilder.Append(tasksql);
                DataTable taskdt = dbHelper.ExecuteDataSet(taskcarBuilder.ToString()).Tables[0];
                #endregion

                string CarNumbers = string.Empty;
                foreach (DataRow dr in taskdt.Rows)
                {
                    CarNumbers += dr["CarNumber"].ToString() + ",";
                }

                CarNumbers = CarNumbers.TrimEnd(",".ToCharArray());

                #region 更新任务与订单关系表
                StringBuilder taskorderBuilder = new StringBuilder();
                string ordersql = @"UPDATE dbo.YW_TaskOrderRelation SET  CarNumber='" + CarNumbers + "' WHERE TaskId='" + taskid + "' AND id=" + TaskOrderId;
                taskorderBuilder.Append(ordersql);

                dbHelper.ExecuteNonQuery(taskorderBuilder.ToString());
            }
            #endregion
        }


        /// <summary>
        /// 配送任务信息
        /// </summary>
        /// <param name="orderdt"></param>
        /// <returns></returns>
        public string SaveYW_TaskInfo(DataTable orderdt, int CurUserId, string GridCode)
        {

           
            try
            {
                CPGrid grid = CPGridEngine.Instance(CurUserId).GetGrid(GridCode, false, false);
                DbHelper dbHelper = new DbHelper(grid.DbIns, CPAppContext.CurDbType());

                #region  最新任务表信息
                //StringBuilder stringBuilder = new StringBuilder();
                //string sql = @"select isnull(MAX(Id),0) id from dbo.YW_TaskInfo";
                //stringBuilder.Append(sql);
                //DataTable dt = dbHelper.ExecuteDataSet(stringBuilder.ToString()).Tables[0];
                //
                //int id = Convert.ToInt32(dt.Rows[0]["id"]) + 1;
                //string TaskId = string.Empty;
                //string yearstr = DateTime.Now.Year.ToString();
                //string monthstr = DateTime.Now.Month.ToString();
                //string daystr = DateTime.Now.Day.ToString();
                //TaskId = yearstr + monthstr + daystr + RightZeroReplaceChar(6, id.ToString());
                #endregion
                int autoIndex;
                string TaskId = CPAutoNumHelper.Instance().GetNextAutoNum("TaskId", out autoIndex);

                int total = 0;
                int loss = 0;
                DateTime plandate = Convert.ToDateTime("1999-01-01");
                foreach (DataRow dr in orderdt.Rows)
                {
                    plandate = (plandate < Convert.ToDateTime(dr["WaitDeliveryTime"])) ? Convert.ToDateTime(dr["WaitDeliveryTime"]) : plandate;
                    total += (dr["TotalCount"].ToString() == "") ? 0 : Convert.ToInt32(dr["TotalCount"]);
                    loss += (dr["OrderRoadLoss"].ToString() == "") ? 0 : Convert.ToInt32(dr["OrderRoadLoss"]);
                }
                #region 配送任务基本信息
                StringBuilder taskBuilder = new StringBuilder();                
                string taskordersql = @"insert into dbo.YW_TaskInfo
                                  (TaskId,PlanDeliveryDate,DeliveryTaskStatus,CreateUseRId,CreateTime,OrderChickTotalCount,OrderLossChickTotalCount)
                                  VALUES";
                taskordersql += "('" + TaskId + "','" + plandate + "',-1," + CurUserId + ",'" + DateTime.Now + "'," + total + "," + loss + ")";
                taskBuilder.Append(taskordersql);


                if (taskBuilder.Length > 0)
                {
                    dbHelper.ExecuteNonQuery(taskBuilder.ToString());
                }

                #endregion
                return TaskId;
            }
            catch (Exception ex)
            {
                return null;
            }

        }

        /// <summary>
        /// 通过用户编码获取用户信息
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="CurUserId"></param>
        /// <param name="GridCode"></param>
        /// <returns></returns>
        public DataTable GetCP_User(int userid, int CurUserId, string GridCode)
        {
            CPGrid grid = CPGridEngine.Instance(CurUserId).GetGrid(GridCode, false, false);
            DbHelper dbHelper = new DbHelper("CPOrganIns", CPAppContext.CurDbType());

            StringBuilder userBuilder = new StringBuilder();
            string usersql = @"select UserId,LoginName,UserPwd,UserName,MobilePhone from dbo.CP_User where userid=" + userid;
            userBuilder.Append(usersql);
            DataTable dt = dbHelper.ExecuteDataSet(userBuilder.ToString()).Tables[0];
            return dt;
        }

        /// <summary>
        ///获取绑定主键信息
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public IList<string> ReadPrimarykeyListDataAndFormat(CPGrid item)
        {
            IList<string> col = new List<string>();
            item.ColumnCol.ForEach(t =>
            {
                if (t.ColumnType != CPGridEnum.ColumnTypeEnum.CheckBox && t.ColumnType != CPGridEnum.ColumnTypeEnum.Radio) return ;             
                DbHelper dbHelper = new DbHelper(t.FieldEnumDataIns, CPAppContext.CurDbType());
                string strSql = t.FieldEnumDataSource;
                if (strSql == "") return ;
                try
                {

                    strSql = CPExpressionHelper.Instance.RunCompile(strSql);
                    DataTable dt = dbHelper.ExecuteDataTable(strSql);
                    DataTable dtNew = new DataTable();
                    dtNew.Columns.Add(new DataColumn() { ColumnName = "id", DataType = typeof(string) });

                    foreach (DataRow dr in dt.Rows)
                    {
                        DataRow newDR = dtNew.NewRow();
                        DataTable tb = dr.Table;
                        foreach (DataColumn column in tb.Columns)
                        {
                            newDR["id"] = dr[0];
                            break;
                        }
                        dtNew.Rows.Add(newDR);
                    }
                    if (dtNew.Rows.Count == 0) return ;
                    col.Add(CPUtils.DataTable2Json(dtNew));
                }
                catch (Exception ex)
                {
                    throw new Exception("执行sql语句:" + strSql + "出错，详细信息为：" + ex.Message);
                }
            });
            return col;
        }

        /// <summary>
        /// 数据变更，清除原始数据，新增变更数据
        /// </summary>
        /// <param name="taskid"></param>
        /// <param name="CurUserId"></param>
        /// <param name="GridCode"></param>
        /// <returns></returns>
        public CPWebApiBaseReturnEntity DeleteTaskInfoRelation(string taskid,string GivetohallId, int CurUserId, string GridCode)
        {

            CPWebApiBaseReturnEntity re = new CPWebApiBaseReturnEntity();
            try
            {
                CPGrid grid = CPGridEngine.Instance(CurUserId).GetGrid(GridCode, false, false);
                DbHelper dbHelper = new DbHelper(grid.DbIns, CPAppContext.CurDbType());


                #region 删除任务与车关系表
                StringBuilder taskcarBuilder = new StringBuilder();
                string carsql = @"delete from DBO.YW_TaskCarRelation where taskid='" + taskid + "'  and TaskOrderId in (select id from DBO.YW_TaskOrderRelation where taskid='" + taskid + "'  and GivetohallId='" + GivetohallId + "')";
                taskcarBuilder.Append(carsql);
                dbHelper.ExecuteNonQuery(taskcarBuilder.ToString());
                #endregion


                #region 删除任务与订单关系表
                StringBuilder taskorderBuilder = new StringBuilder();
                string ordersql = @"delete from DBO.YW_TaskOrderRelation where taskid='" + taskid + "'  and GivetohallId='" + GivetohallId + "'";
                taskorderBuilder.Append(ordersql);
                dbHelper.ExecuteNonQuery(taskorderBuilder.ToString());
                #endregion

               


                #region 更新任务表变更时间
                StringBuilder taskBuilder = new StringBuilder();
                string tasksql = @"UPDATE DBO.YW_TaskInfo SET  ModifyTime='" + DateTime.Now + "' where taskid='" + taskid + "'";
                taskBuilder.Append(tasksql);
                dbHelper.ExecuteNonQuery(taskBuilder.ToString());
                #endregion


                re.Result = true;
                return re;
            }
            catch (Exception ex)
            {
                re.Result = false;
                re.ErrorMsg = ex.Message.ToString();
                return re;
            }
        }

        /// <summary>
        /// 保存订单任务关系信息
        /// </summary>
        /// <param name="taskid"></param>
        /// <param name="GivetohallId"></param>
        /// <param name="Sort"></param>
        /// <param name="CurUserId"></param>
        /// <param name="GridCode"></param>
        /// <param name="OrderId"></param>
        /// <returns></returns>
        public CPWebApiBaseReturnEntity SaveOrderForTaskInfoRelation(string taskid, string OrderId, string GivetohallId, int Sort, int CurUserId, string GridCode,DataRow orderrow )
        {
            CPWebApiBaseReturnEntity re = new CPWebApiBaseReturnEntity();
            try
            {
                CPGrid grid = CPGridEngine.Instance(CurUserId).GetGrid(GridCode, false, false);
                DbHelper dbHelper = new DbHelper(grid.DbIns, CPAppContext.CurDbType());
                re.Result = true;

                #region 判断任务下是否包含次订单
                StringBuilder taskorderBuilder = new StringBuilder();
                string ordersql = @"select Id,TaskId,OrderId,GivetohallId from YW_TaskOrderRelation where TaskId='" + taskid + "'  and GivetohallId='" + GivetohallId + "'";
                taskorderBuilder.Append(ordersql);
                DataTable datat = dbHelper.ExecuteDataSet(taskorderBuilder.ToString()).Tables[0];
                #endregion




                #region 当不存在时更新
                if (datat.Rows.Count > 0) {
                    //当存在时更新
                    StringBuilder newBuilder = new StringBuilder();
                    string  newsql= @"update dbo.YW_TaskOrderRelation  set Sort="+Sort+"  where TaskId='" + taskid + "'  and GivetohallId='" + GivetohallId + "'";
                    newBuilder.Append(newsql);
                    if (newBuilder.Length > 0)
                    {
                        dbHelper.ExecuteNonQuery(newBuilder.ToString());
                    }
                    return re;
                }

                
                int autoIndex;
                long TaskOrderId = Convert.ToInt64(CPAutoNumHelper.Instance().GetNextAutoNum("TaskOrderId", out autoIndex));


                StringBuilder taskBuilder = new StringBuilder();
                string taskordersql = @"insert into dbo.YW_TaskOrderRelation
                                  (Id,TaskId,OrderId,Sort,OrderDeliveryStatus,IsMore,IsChange,BaseId,BaseName,HallId,HallName,GivetohallId,VarietyId,VarietyName,GoodsId,GoodsName)
                                  VALUES";
                taskordersql += "(" + TaskOrderId + ",'" + taskid + "','" + orderrow["OrderId"] + "'," + Sort + ",0,0,0,";
                taskordersql += " '" + orderrow["BaseId"] + "','" + orderrow["BaseName"] + "','" + orderrow["HallId"] + "','" + orderrow["HallName"] + "',";
                taskordersql += "'" + GivetohallId + "','" + orderrow["VarietyId"] + "','" + orderrow["VarietyName"] + "','" + orderrow["GoodsId"] + "','" + orderrow["GoodsName"] + "')";
                taskBuilder.Append(taskordersql);
                if (taskBuilder.Length > 0)
                {
                    dbHelper.ExecuteNonQuery(taskBuilder.ToString());
                }
                #endregion
                return re;
            }
            catch (Exception ex)
            {
                re.Result = false;
                re.ErrorMsg = ex.Message.ToString();
                return re;
            }
        }
        /// <summary>
        /// 清除任务分配车辆表信息
        /// </summary>
        /// <param name="taskid"></param>
        /// <returns></returns>
        public CPWebApiBaseReturnEntity DeleteYW_TaskCarRelation(string taskid, int CurUserId, string GridCode)
        {
            CPWebApiBaseReturnEntity re = new CPWebApiBaseReturnEntity();
            try
            {
                CPGrid grid = CPGridEngine.Instance(CurUserId).GetGrid(GridCode, false, false);
                DbHelper dbHelper = new DbHelper(grid.DbIns, CPAppContext.CurDbType());
                #region 删除任务与车关系表
                StringBuilder taskcarBuilder = new StringBuilder();
                string carsql = @"delete from DBO.YW_TaskCarRelation where taskid='" + taskid + "' ";
                taskcarBuilder.Append(carsql);
                dbHelper.ExecuteNonQuery(taskcarBuilder.ToString());
                #endregion

                re.Result = true;
                return re;
            }
            catch (Exception ex)
            {
                re.Result = false;
                re.ErrorMsg = ex.Message.ToString();
                return re;
            }
        }
        /// <summary>
        /// 通过任务编码获取任务与订单关系信息
        /// </summary>
        /// <param name="taskid"></param>
        /// <param name="CurUserId"></param>
        /// <param name="GridCode"></param>
        /// <returns></returns>
        public DataTable GetYW_TaskOrderRelation(string taskid, int CurUserId, string GridCode)
        {

            CPGrid grid = CPGridEngine.Instance(CurUserId).GetGrid(GridCode, false, false);
            DbHelper dbHelper = new DbHelper(grid.DbIns, CPAppContext.CurDbType());

            StringBuilder userBuilder = new StringBuilder();
            string usersql = @"select a.TaskId,a.Id,a.GivetohallId,a.OrderId,b.BaseId,b.BaseName,b.HallId,b.HallName,b.VarietyId,b.VarietyName,b.GoodsId,b.GoodsName,b.BuyChickCount,b.HallLossCount  from dbo.YW_TaskOrderRelation a inner  join dbo.YW_GivetohallInfo b on a.GivetohallId=b.Id  where a.taskid='" + taskid+"'";
            userBuilder.Append(usersql);
            DataTable dt = dbHelper.ExecuteDataSet(userBuilder.ToString()).Tables[0];
            return dt;
        }
        /// <summary>
        /// 保存任务车辆信息
        /// </summary>
        /// <param name="dr"></param>
        /// <param name="CurUserId"></param>
        /// <param name="GridCode"></param>
        /// <returns></returns>
        public CPWebApiBaseReturnEntity SaveYW_TaskCarRelation(DataRow dr, int CurUserId, string GridCode, DataTable mainuserTable, DataTable assuserTable, int carid,int BuyClickCount,int HallLossCount)
        {
            CPWebApiBaseReturnEntity re = new CPWebApiBaseReturnEntity();
            try
            {
                CPGrid grid = CPGridEngine.Instance(CurUserId).GetGrid(GridCode, false, false);
                DbHelper dbHelper = new DbHelper(grid.DbIns, CPAppContext.CurDbType());

                #region  获取车辆信息
                StringBuilder stringBuilder = new StringBuilder();
                string sql = @"select Id,CarNumber,MainDriverId,MainDriverName,AssistantDriverId,AssistantDriverName,MainDriverTel,AssistantDriverTel,
                               CarType,SensorId,GPSId,CarTeamNumber,CarTeamName,BaseId,BaseName,CreateUserId,CreateTime,ModifyUserId,ModifyTime
                               from dbo.BD_CarInfo where id=" + Convert.ToInt32(carid);
                stringBuilder.Append(sql);
                DataTable dt = dbHelper.ExecuteDataSet(stringBuilder.ToString()).Tables[0];
                #endregion


                #region 新增任务与车关系表

                int buyClick = Convert.ToInt32(dr["BuyChickCount"]);
                if (BuyClickCount > buyClick || BuyClickCount == 0) BuyClickCount = buyClick;

                int hallClick = Convert.ToInt32(dr["HallLossCount"]);
                if (HallLossCount > hallClick || HallLossCount == 0) HallLossCount = hallClick;


                StringBuilder taskcarBuilder = new StringBuilder();
                string carsql = @"INSERT INTO dbo.YW_TaskCarRelation
                                  (TaskId,OrderId,TaskOrderId,BaseId,BaseName,HallId,HallName,CarNumber,SensorId,GPSId,MainDriverId,MainDriverName,MainDriverTel,AssistantDriverId,AssistantDriverName,AssistantDriverTel,OrderChickCountInCar,HallLossCount)
                                  VALUES
                                  ('" + dr["TaskId"] + "','" + dr["OrderId"] + "'," + dr["Id"] + ",'" + dr["BaseId"] + "','" + dr["BaseName"] + "','" + dr["HallId"] + "','" + dr["HallName"] + "','" + dt.Rows[0]["CarNumber"] + "'," +
                                  "'" + dt.Rows[0]["SensorId"] + "','" + dt.Rows[0]["GPSId"] + "','" + mainuserTable.Rows[0]["UserId"] + "','" + mainuserTable.Rows[0]["UserName"] + "','" + mainuserTable.Rows[0]["MobilePhone"] + "','" + assuserTable.Rows[0]["UserId"] + "'," +
                                  "'" + assuserTable.Rows[0]["UserName"] + "','" + assuserTable.Rows[0]["MobilePhone"] + "'," + BuyClickCount + "," + HallLossCount + ")";
                taskcarBuilder.Append(carsql);
                dbHelper.ExecuteNonQuery(taskcarBuilder.ToString());
                #endregion

                re.Result = true;
                return re;
            }
            catch (Exception ex)
            {
                re.Result = false;
                re.ErrorMsg = ex.Message.ToString();
                return re;
            }
        }

        public IList<string> CustomerAddressForTaskid(string taskid)
        {
           
            DbHelper dbHelper = new DbHelper("CPYKWL", CPAppContext.CurDbType());
            IList<string> col = new List<string>();
            StringBuilder stringBuilder = new StringBuilder();
            //string sql = @"select distinct OrderId,CustomerState+CustomerCity+CustomerDistrict+CustomerAddress as CustomerAddress,ISNULL(Sort,1) Sort from  dbo.YW_TaskOrderRelation a inner join dbo.YW_OrderInfo b 
            //             on a.OrderId=b.OrderCode where TaskId='" + taskid+ "' order by ISNULL(Sort,1) ,OrderId asc";           
            string sql = "";
            if (!taskid.Contains("_"))
            {
                sql = @"select distinct OrderId,CustomerState+CustomerCity+CustomerDistrict+CustomerAddress as CustomerAddress,CustomerName,ISNULL(Sort,1) Sort from  dbo.YW_TaskOrderRelation a inner join dbo.YW_OrderInfo b 
                         on a.OrderId=b.OrderCode where TaskId='" + taskid + "' order by ISNULL(Sort,1) ,OrderId asc";
            }
            else//add by zzh 20181206 用于展示按车辆展示配送规划路径信息。
            {
                sql = @"select distinct a.OrderId,CustomerState+CustomerCity+CustomerDistrict+CustomerAddress as CustomerAddress,CustomerName,ISNULL(Sort,1) Sort from  dbo.YW_TaskOrderRelation a inner join dbo.YW_OrderInfo b 
                         on a.OrderId=b.OrderCode inner join dbo.YW_TaskCarRelation c on c.TaskOrderId=a.Id  where a.TaskId='" + taskid.Split('_')[0] + "'  and c.CarNumber='" + taskid.Split('_')[1] + "'  order by ISNULL(Sort,1) ,OrderId asc";
            }

            try
            {
                stringBuilder.Append(sql);
                DataTable dt = dbHelper.ExecuteDataSet(stringBuilder.ToString()).Tables[0];
                DataTable dtNew = new DataTable();
                dtNew.Columns.Add(new DataColumn() { ColumnName = "OrderId", DataType = typeof(string) });
                dtNew.Columns.Add(new DataColumn() { ColumnName = "CustomerAddress", DataType = typeof(string) });
                dtNew.Columns.Add(new DataColumn() { ColumnName = "CustomerName", DataType = typeof(string) });
                foreach (DataRow dataRow in dt.Rows)
                {
                    DataRow newDR = dtNew.NewRow();
                    DataTable tb = dataRow.Table;
                    var mark = 0;
                    if (dtNew.Rows.Count > 0)
                    {
                        foreach (DataRow dr in dtNew.Rows)
                        {
                            if (dr["CustomerAddress"].ToString() == dataRow[1].ToString()|| dr["OrderId"].ToString() == dataRow[0].ToString()) { mark = 1; break; }
                        }
                    }
                    if (mark == 1) continue;
                    newDR["OrderId"] = dataRow[0];
                    newDR["CustomerAddress"] = dataRow[1];
                    newDR["CustomerName"] = dataRow[2];
                    dtNew.Rows.Add(newDR);
                }
                if (dtNew.Rows.Count == 0) return null;
                col.Add(CPUtils.DataTable2Json(dtNew));
            }
            catch (Exception ex)
            {
                throw new Exception("执行sql语句:" + sql + "出错，详细信息为：" + ex.Message);
            }
            return col;
        }
        /// <summary>
        /// 获取部门信息
        /// </summary>
        /// <param name="DepId"></param>
        /// <returns></returns>
        public IList<string> GetCP_Dep(string DepIds)
        {
            string[] depArray = DepIds.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
            IList<string> dep = new List<string>();
            foreach (string DepId in depArray)
            {
                if (dep.Count > 0) return dep;
                DbHelper dbHelper = new DbHelper("CPOrganIns", CPAppContext.CurDbType());              
                StringBuilder stringBuilder = new StringBuilder();
                string sql = @" select DepId,DepName,DepShortName,DepShortCode,ParentId,DepType,DepState,ShowOrder,
                            longitude,latitude,DepAddress from dbo.CP_Dep where DepId in (select ParentId from dbo.CP_Dep where DepId=" + Convert.ToInt32( DepId) + "  ) and DepType=6";
                try
                {
                    stringBuilder.Append(sql);
                    DataTable dt = dbHelper.ExecuteDataSet(stringBuilder.ToString()).Tables[0];
                    DataTable dtNew = new DataTable();
                    dtNew.Columns.Add(new DataColumn() { ColumnName = "longitude", DataType = typeof(string) });
                    dtNew.Columns.Add(new DataColumn() { ColumnName = "latitude", DataType = typeof(string) });
                    dtNew.Columns.Add(new DataColumn() { ColumnName = "DepAddress", DataType = typeof(string) });
                    foreach (DataRow dataRow in dt.Rows)
                    {
                        DataRow newDR = dtNew.NewRow();
                        DataTable tb = dataRow.Table;
                        newDR["longitude"] = dataRow["longitude"];
                        newDR["latitude"] = dataRow["latitude"];
                        newDR["DepAddress"] = dataRow["DepAddress"];
                        dtNew.Rows.Add(newDR);
                    }
                    if (dtNew.Rows.Count == 0) continue;
                    dep.Add(CPUtils.DataTable2Json(dtNew));
                }
                catch (Exception ex)
                {
                    throw new Exception("执行sql语句:" + sql + "出错，详细信息为：" + ex.Message);
                }
            }
            return dep;
        }
        /// <summary>
        /// 订单调整数据保存
        /// </summary>
        /// <param name="taskid"></param>
        /// <param name="oldorderid"></param>
        /// <param name="neworderid"></param>
        /// <returns></returns>

        public CPWebApiBaseReturnEntity SaveYW_TaskChangeRelation(string taskid,string oldorderid,string neworderid)
        {
            CPWebApiBaseReturnEntity re = new CPWebApiBaseReturnEntity();
            try
            {
                DbHelper dbHelper = new DbHelper("CPYKWL", CPAppContext.CurDbType());
                IList<string> col = new List<string>();
                StringBuilder stringBuilder = new StringBuilder();
                string userId = CPExpressionHelper.Instance.RunCompile("${CPUser.UserId()}");
                string sql = @"insert into dbo.YW_TaskChangeRelation(TaskId,OldOrderId,NewOrderId,CreateUserId,CreateTime)
                              values
                              ('" + taskid + "','" + oldorderid + "','" + neworderid + "','" + userId + "','" + DateTime.Now + "')" +
                              " update dbo.YW_TaskInfo set ChangeStatus=2 where taskid='" + taskid + "'";

                stringBuilder.Append(sql);
                dbHelper.ExecuteNonQuery(stringBuilder.ToString());
                re.Result = true;
                return re;
            }
            catch (Exception ex)
            {
                re.Result = false;
                re.ErrorMsg = ex.Message.ToString();
                return re;
            }
        }

        /// <summary>
        /// 删除树，任务信息
        /// </summary>
        /// <param name="taskid"></param>
        /// <returns></returns>
        public CPWebApiBaseReturnEntity DeleteTaskInfo(string taskid)
        {
            CPWebApiBaseReturnEntity re = new CPWebApiBaseReturnEntity();
            try
            {
                DbHelper dbHelper = new DbHelper("CPYKWL", CPAppContext.CurDbType());
                StringBuilder stringBuilder = new StringBuilder();
                string sql = @"delete from dbo.YW_TaskCarRelation where taskid='" + taskid + "';" +
                              "delete from dbo.YW_TaskOrderRelation where taskid='" + taskid + "';" +
                              "delete from dbo.YW_TaskInfo where  taskid='" + taskid + "'";
                stringBuilder.Append(sql);
                dbHelper.ExecuteNonQuery(stringBuilder.ToString());
                re.Result = true;
                return re;
            }
            catch (Exception ex)
            {
                re.Result = false;
                re.ErrorMsg = ex.Message.ToString();
                return re;
            }
        }

        /// <summary>
        /// 删除树，任务车辆信息
        /// </summary>
        /// <param name="taskid"></param>
        /// <returns></returns>
        public CPWebApiBaseReturnEntity DeleteTaskCarRelationInfo(string taskid,string carnumber)
        {
            CPWebApiBaseReturnEntity re = new CPWebApiBaseReturnEntity();
            try
            {
                DbHelper dbHelper = new DbHelper("CPYKWL", CPAppContext.CurDbType());
                StringBuilder stringBuilder = new StringBuilder();
                string sql = @"delete from dbo.YW_TaskOrderRelation where taskid='"+ taskid + "' and id in (select TaskOrderId from dbo.YW_TaskCarRelation where TaskId='"+ taskid + "' and CarNumber='"+ carnumber + "');" +
                              "delete from dbo.YW_TaskCarRelation  where  TaskId='" + taskid + "' and CarNumber='" + carnumber + "'";
                              
                stringBuilder.Append(sql);
                dbHelper.ExecuteNonQuery(stringBuilder.ToString());
                re.Result = true;
                return re;
            }
            catch (Exception ex)
            {
                re.Result = false;
                re.ErrorMsg = ex.Message.ToString();
                return re;
            }
        }

        /// <summary>
        /// 删除树，任务订单信息
        /// </summary>
        /// <param name="taskid"></param>
        /// <returns></returns>
        public CPWebApiBaseReturnEntity DeleteTaskOrderRelationInfo(string taskid, string carnumber,string orderid)
        {
            CPWebApiBaseReturnEntity re = new CPWebApiBaseReturnEntity();
            try
            {
                DbHelper dbHelper = new DbHelper("CPYKWL", CPAppContext.CurDbType());
                StringBuilder stringBuilder = new StringBuilder();
                string sql = string.Empty;
                if (carnumber == "未设置")
                    sql = "delete from dbo.YW_TaskOrderRelation  where taskid='" + taskid + "' and OrderId='" + orderid + "'";
                else
                {
                    sql = @"delete from dbo.YW_TaskOrderRelation where  taskid='" + taskid + "' and id in (select TaskOrderId from dbo.YW_TaskCarRelation where TaskId='" + taskid + "' and CarNumber='" + carnumber + "') and OrderId='" + orderid + "';" +
                          "delete from dbo.YW_TaskCarRelation where  TaskId = '" + taskid + "'  and OrderId = '" + orderid + "' and  CarNumber='" + carnumber + "'  ";                   
                }
                stringBuilder.Append(sql);
                dbHelper.ExecuteNonQuery(stringBuilder.ToString());
                re.Result = true;
                return re;
            }
            catch (Exception ex)
            {
                re.Result = false;
                re.ErrorMsg = ex.Message.ToString();
                return re;
            }
        }

        /// <summary>
        /// 返回指定长度字符，长度不够用0代替
        /// 【'000012','000008'】
        /// </summary>
        /// <param name="length"></param>
        /// <param name="chart"></param>
        /// <returns></returns>
        private string RightZeroReplaceChar(int length, string charstr)
        {
            int charlength = charstr.Length;
            if (charlength > length) return null;
            int difflength = length - charlength;
            string str = string.Empty;
            for (int i = 0; i < difflength; i++)
            {
                str += "0";
            }
            str += charstr;
            return str;
        }


    }
}
